#ifndef EXPORTUTIL_H
#define EXPORTUTIL_H

#include <QCoreApplication>
#include <QtDebug>
#include <string>
#include <QDateTime>
#include <QFile>
#include <QDir>
#include <QAxObject>
#include <QColor>
#include <QApplication>
#include <QStandardItemModel>
//#include "databaseutil.h"

class ExportUtil{
   QAxObject  *pApplication;
   QAxObject *pWorkBooks;
   QAxObject *pWorkBook;
   QAxObject *pSheets;
   QAxObject *pSheet;
private:
    //DBSql dbsql;   
    QString excelPath  =  QApplication::applicationDirPath() + "/conf/template.xls";
    bool ExportInit(){
        /*pApplication = new QAxObject();
        pApplication->setControl("Excel.Application");//连接Excel控件
        pApplication->dynamicCall("SetVisible(bool)", false);//false不显示窗体
        pApplication->setProperty("DisplayAlerts", false);//*/
    }
    void exporterror(bool flag,QString sql,QString sqlname){
          qDebug() << sqlname+"成功！" << sql;
    }
public:
    // 构造函数，初始化数据库连接
    ExportUtil(){
       // this->ExportInit();
    }  



    //新建一个EXCEL
    void newExcel(const QString &fileName,int n_sheet)
    {
        pApplication = new QAxObject("Excel.Application");
        if (pApplication == nullptr) {
           qWarning("pApplication\n");
           return;
        }
        pApplication->dynamicCall("SetVisible(bool)",false);// false不显示窗体
        pApplication->setProperty("DisplayAlerts",false);// 不显示任何警告信息
        pWorkBooks = pApplication->querySubObject("Workbooks");
        QFile file(fileName);
        if (file.exists()) {
           pWorkBook = pWorkBooks->querySubObject("Open(const QString&)",fileName);
        } else {
            //此处需要做异常处理，如客户端没有EXCEL会报错
           pWorkBooks->dynamicCall("Add");
           pWorkBook = pApplication->querySubObject("ActiveWorkBook");
        }
        pSheets = pWorkBook->querySubObject("Sheets");
        pSheet = pSheets->querySubObject("Item(int)",n_sheet);

    }



    void saveExcel(const QString &fileName)
    {
//        pSheet->dynamicCall("Save()");
//        pSheet->dynamicCall("Close()");
//        pWorkBook->dynamicCall("Save()");
//        pWorkBook->dynamicCall("Close()");
//        workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(filepath));//保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
//        workbook->dynamicCall("Close()");//关闭工作簿
//        excel->dynamicCall("Quit()");//关闭excel
//        delete excel;
//        excel=NULL;
        pWorkBook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(fileName));
        pWorkBook->dynamicCall("Close()");//关闭工作簿
        pApplication->dynamicCall("Quit()");
        delete pApplication;
        pApplication   = NULL;

    }

    void  freeExcel()
    {
        if (pApplication != NULL)
        {
            pApplication->dynamicCall("Quit()");
            delete pApplication;
            pApplication = NULL;
        }
    }

    //向Excel单元格中写入数据,可以设置单元格的属性
    void setCellValue1(int row, int column, const QString &value)
    {
        QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", row, column);

        pRange->dynamicCall("Value", value);
        //内容居中
        pRange->setProperty("HorizontalAlignment", -4108);
        pRange->setProperty("VerticalAlignment", -4108);
        pRange->setProperty("RowHeight", 50); //设置单元格行高
        pRange->setProperty("ColumnWidth", 30); //设置单元格列宽

        pRange->setProperty("WrapText", true); //内容过多，自动换行
     //   pRange->dynamicCall("ClearContents()"); //清空单元格内容

        QAxObject* interior =pRange->querySubObject("Interior");
        interior->setProperty("Color", QColor(0, 255, 0)); //设置单元格背景色（绿色）
        QAxObject* border = pRange->querySubObject("Borders");
        border->setProperty("Color", QColor(0, 0, 255)); //设置单元格边框色（蓝色）
        QAxObject *font = pRange->querySubObject("Font"); //获取单元格字体
        font->setProperty("Name", QStringLiteral("华文彩云")); //设置单元格字体
        font->setProperty("Bold", true); //设置单元格字体加粗
        font->setProperty("Size", 20); //设置单元格字体大小
        font->setProperty("Italic", true); //设置单元格字体斜体
        font->setProperty("Underline", 2); //设置单元格下划线
        font->setProperty("Color", QColor(255, 0, 0)); //设置单元格字体颜色（红色）

        if(row == 1){
            QAxObject *font = pRange->querySubObject("Font"); //获取单元格字体
            font->setProperty("Bold",true);
        }
    }

    void setCellValue(int row, int column, const QString &value)
    {
        QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", row, column);
        pRange->dynamicCall("Value", value);


       // pRange->setProperty("WrapText", true); //内容过多，自动换行
        if(row == 1){
            QAxObject *font = pRange->querySubObject("Font"); //获取单元格字体
            font->setProperty("Bold",true);
            //标题居中
            pRange->setProperty("HorizontalAlignment", -4108);
            pRange->setProperty("VerticalAlignment", -4108);
        }

    }

    //增加1个Worksheet，需要两个以上sheet时调用，默认有一个
    void appendSheet(const QString &sheetName)
    {
        int cnt = 1;
        QAxObject *pLastSheet = pSheets->querySubObject("Item(int)", cnt);
        pSheets->querySubObject("Add(QVariant)", pLastSheet->asVariant());
        pSheet = pSheets->querySubObject("Item(int)", cnt);
        pLastSheet->dynamicCall("Move(QVariant)", pSheet->asVariant());
        pSheet->setProperty("Name", sheetName);
    }

    QString export_file(){

        return "true";
    }


    //导出EXCEL，传入EXCEL头，查询SQL;
    QString export_excel(QStandardItemModel *m_tableModel,QString filepath){
       // QString dtStr =QApplication::applicationDirPath() + "/conf/"+ QDateTime::currentDateTime().toString("yyyyMMddHHmmss")+".xlsx";


        newExcel(filepath,1);
        setCellValue(1,1,"日期时间");
        setCellValue(1,2,"杆长");
        setCellValue(1,3,"方位角");
        setCellValue(1,4,"倾角");
        setCellValue(1,5,"工具角");
        int rows = m_tableModel->rowCount();
        for(int i=0;i<rows;i++){
            setCellValue(i+2,1,m_tableModel->item(i,0)->text());
            setCellValue(i+2,2,m_tableModel->item(i,1)->text());
            setCellValue(i+2,3,m_tableModel->item(i,2)->text());
            setCellValue(i+2,4,m_tableModel->item(i,3)->text());
           //setCellValue(i+2,5,m_tableModel->item(i,4)->text());
        }
        saveExcel(filepath);
        return "";

    }

    QString save_file(QStandardItemModel *m_tableModel){

        newExcel(excelPath,2);

        int rows = m_tableModel->rowCount();
        for(int i=0;i<rows;i++){
            setCellValue(i+1,1,m_tableModel->item(i,0)->text());
            setCellValue(i+1,2,m_tableModel->item(i,1)->text());
            setCellValue(i+1,3,m_tableModel->item(i,2)->text());
            setCellValue(i+1,4,m_tableModel->item(i,3)->text());
            setCellValue(i+1,5,m_tableModel->item(i,4)->text());
        }

       saveExcel(excelPath);



        return "true";
    }

    QString read_file(QStandardItemModel *m_tableModel){
        newExcel(excelPath,2);

        QAxObject* range = pSheet->querySubObject("UsedRange"); //获取该sheet的使用范围对象
        QVariant var = range->dynamicCall("Value");
        delete range;
        QVariantList dataList = var.toList();  //得到表格中的所有数据

        pWorkBook->dynamicCall("Close()"); //关闭工作簿
        pApplication->dynamicCall("Quit()"); //退出进程
        pApplication->dynamicCall("Quit()");
        delete pApplication;
        pApplication   = NULL;
        if (dataList.isEmpty()) {
            return "";
        }
        m_tableModel->clear();

        for (int i = 1; i < dataList.size(); i++) {
            QVariantList rowData = dataList[i].toList();
            m_tableModel->setItem(i - 1,0,new QStandardItem(rowData[0].toString().replace(10,1," ")));
            m_tableModel->item(i - 1, 0)->setTextAlignment(Qt::AlignCenter);
            m_tableModel->setItem(i - 1,1,new QStandardItem(rowData[1].toString()));
            m_tableModel->item(i - 1, 1)->setTextAlignment(Qt::AlignCenter);
            m_tableModel->setItem(i - 1,2,new QStandardItem(rowData[2].toString()));
            m_tableModel->item(i - 1, 2)->setTextAlignment(Qt::AlignCenter);
            m_tableModel->setItem(i - 1,3,new QStandardItem(rowData[3].toString()));
            m_tableModel->item(i - 1, 3)->setTextAlignment(Qt::AlignCenter);
            m_tableModel->setItem(i - 1,4,new QStandardItem(rowData[4].toString()));
            m_tableModel->item(i - 1, 4)->setTextAlignment(Qt::AlignCenter);
        }



        return "true";
    }



};
#endif // EXPORTUTIL_H
